package Dao;

import Util.DruidUtils;
import bean.PageBean;
import bean.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaolmpl implements StudentDao{
    @Override
    public List<Student> studentShow(PageBean pageBean) {
        List<Student> list = null;
        Connection connection = null;
        StringBuilder stringBuilder = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DruidUtils.getConnection ();
            //使用可变字符串用于后面拼接字符
            stringBuilder = new StringBuilder ("select * from t_student");
            // 当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit " + pageBean.getStart() + "," + pageBean.getRow());
            }
            // 获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement (stringBuilder.toString ());
            resultSet = preparedStatement.executeQuery ();
            // 5.将结果集中的数据取出并放到List集合中返回
            list = new ArrayList<> ();
            while (resultSet.next ()) {
                Student student = new Student (resultSet.getInt ("id"),resultSet.getString ("name"),
                        resultSet.getString ("sex"),resultSet.getString ("birthday"),resultSet.getString ("email"),
                        resultSet.getString ("comments"));
                list.add (student);
            }
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement,resultSet);
        }
        return list;
    }

    //新增
    @Override
    public int studentAdd(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //获取连接
            connection = DruidUtils.getConnection ();
            //编写sql
            String sql = "insert into t_student values(null,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1,student.getName ());
            preparedStatement.setString (2,student.getSex ());
            preparedStatement.setString (3,student.getBirthday ());
            preparedStatement.setString (4,student.getEmail ());
            preparedStatement.setString (5,student.getComments ());
            return preparedStatement.executeUpdate ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            //关闭流
            DruidUtils.close (connection,preparedStatement);
        }
        return 0;
    }
    //删除
    @Override
    public int studentDelete(String[] ids) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DruidUtils.getConnection ();
            StringBuilder sb = new StringBuilder ();
            for(int i = 0;i<ids.length;++i){
                if(i==ids.length-1){
                    sb.append (ids[i]);
                } else {
                    sb.append (ids[i]).append (",");
                }
            }
            String sql = "delete from t_student where id in ("+ sb + ")";
            preparedStatement = connection.prepareStatement (sql);
            return preparedStatement.executeUpdate ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement);
        }
        return 0;// 表示删除失败
    }
    //修改
    @Override
    public int studentModify(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DruidUtils.getConnection ();
            String sql = "update t_student set name=?,sex=?,birthday=?,email=?,comments=? where id=?";
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1,student.getName ());
            preparedStatement.setString (2,student.getSex ());
            preparedStatement.setString (3,student.getBirthday ());
            preparedStatement.setString (4,student.getEmail ());
            preparedStatement.setString (5,student.getComments ());
            preparedStatement.setInt (6,student.getId ());
            return preparedStatement.executeUpdate ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement);
        }
        return 0;
    }
    //查找
    @Override
    public Student studentFind(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            connection = DruidUtils.getConnection ();
            String sql = "select * from t_student where id = ?";
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setInt (1,id);
            resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ()) {
                 student = new Student (resultSet.getInt ("id"),resultSet.getString ("name"),resultSet.getString ("sex"),
                        resultSet.getString ("birthday"),resultSet.getString ("email"),resultSet.getString ("comments"));
            }
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement,resultSet);
        }
        return student;
    }
}
